﻿CREATE PROCEDURE [dbo].[proc_Project_Task_Finish]
(
	@TaskId uniqueidentifier,
	@ProjectId uniqueidentifier,
	@DepId int
)
As
Begin Tran
	Declare @Title nvarchar(200),@RevTitle nvarchar(200),@n int

	Select @Title=RTrim(IsNull(Title,'')) From Project_Task Where TaskId=@TaskId
	Set @n=CharIndex('退回」',@Title,0)
	
	--处理个人任务
	If @n=0		--正常任务完结
		Update Project_Task Set IsFinish=1,EndDate=getdate() Where TaskId=@TaskId
	Else
	Begin	--也是正常完结，不过这个单子被退回过，需要把“退回”字样处理掉
		Set @RevTitle=Reverse(@Title)
		Set @n=CharIndex('「',@RevTitle,0)
		If @n=0
			Update Project_Task Set IsFinish=1,EndDate=getdate() Where TaskId=@TaskId
		Else
			Update Project_Task Set Title=Left(@Title,Len(@Title)-@n),IsFinish=1,EndDate=getdate() Where TaskId=@TaskId
	End
	
	--处理团队任务
	Declare @TSum int, @TFinishSum int
	--取本项目、指定部门下的所有人的未完成的任务
	Select @TFinishSum=Count(0) From Project_Task Where ProjectId=@ProjectId And ExecDepartmentId=@DepId And TypeId=2 And IsFinish=0
	If(@TFinishSum=0)	--本项目、指定部门下所有人的任务均已完成，更新部门任务(如果有的话)为完成
	Begin
		Update Project_Task Set IsFinish=1,EndDate=GetDate() Where ProjectId=@ProjectId And ExecDepartmentId=@DepId And TypeId=1

		--到此，该部门的任务都处理完了，接下来处理该部门的一系列上级部门(如果有的话)任务
		--通过CTE的递归，得到该部门的所有父部门、祖先部门
		;WITH List1 As(
			Select Id,Parentid From Department Where id=@DepId
			Union All
			Select a.Id,a.Parentid From List1,Department a Where List1.Parentid=a.Id
		)

		--把上步部门里个人任务全完成的，部门任务则自动改成完成
		Update a Set IsFinish=1,EndDate=GetDate() From Project_Task a,(Select Id,
		IsNull((Select Count(0) From Project_Task Where ProjectId=@ProjectId And ExecDepartmentId=L.Id And TypeId=2 And IsFinish=0),0) As TaskCount
		From List1 L) b
		Where a.ProjectId=@ProjectId And a.ExecDepartmentId=b.Id And b.TaskCount=0 And a.TypeId=1
	End
	
	--处理整个项目
	Declare @DSum int, @DFinishSum int
	--取该项目所有未完成的任务(含部门和个人)
	Select @DFinishSum=Count(0) From Project_Task Where ProjectId=@ProjectId And IsFinish=0
	If(@DFinishSum=0)
	Begin
		Update Project Set IsFinish=1,FinishDate=getdate() Where ProjectId=@ProjectId And ProductId Not In(select id from Product where flag=1)	--更新项目为完结(网站项目除外)
		Update Project Set IsFinish=1 Where ProjectId=@ProjectId And ProductId In(select id from Product where flag=1) And FinishDate Is Not Null	--更新项目为完结(早已经完结的网站项目且又被客服下新任务的)，原完结日期不变
	End

	If @@error <> 0  --发生错误
		Begin
			RollBack Tran
			Return
		End
	Else
		Begin
			Commit Tran
			Return    --执行成功
End
